{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "223b8164",
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "from bs4 import BeautifulSoup\n",
    "import pandas as pd\n",
    "\n",
    "def get_html(url):\n",
    "    \"\"\"\n",
    "    获取给定URL的HTML内容。\n",
    "\n",
    "    参数:\n",
    "    - url (str): 要抓取的URL。\n",
    "\n",
    "    返回:\n",
    "    - str: 网页的HTML内容。\n",
    "    \"\"\"\n",
    "    try:\n",
    "        r = requests.get(url)  # 发送GET请求\n",
    "        r.raise_for_status()  # 检查响应状态\n",
    "        r.encoding = r.apparent_encoding  # 根据内容编码设置编码方式\n",
    "        return r.text  # 返回HTML内容\n",
    "    except Exception as e:\n",
    "        print(\"发生异常:\", e)  # 输出异常信息\n",
    "        return None  # 返回None表示出现异常\n",
    "\n",
    "def parse_html(html):\n",
    "    \"\"\"\n",
    "    解析HTML内容，提取所有<tr>标签中的元素并返回DataFrame对象。\n",
    "\n",
    "    参数:\n",
    "    - html (str): 网页的HTML内容。\n",
    "\n",
    "    返回:\n",
    "    - pd.DataFrame: 包含提取数据的DataFrame对象。\n",
    "    \"\"\"\n",
    "    soup = BeautifulSoup(html, 'html.parser')  # 使用BeautifulSoup解析HTML内容\n",
    "    rows = soup.find_all('tr')  # 查找所有<tr>标签\n",
    "    data = []  # 初始化数据列表\n",
    "    max_cols = 0  # 初始化最大列数\n",
    "    for row in rows:\n",
    "        cols = [col.get_text(strip=True) for col in row.find_all(['td'])]  # 提取每行的文本内容\n",
    "        if len(cols) > 0:\n",
    "            data.append(cols)  # 将提取的行添加到数据列表中\n",
    "            if len(cols) > max_cols:\n",
    "                max_cols = len(cols)  # 更新最大列数\n",
    "    # 添加空列以确保每行具有相同的列数\n",
    "    for row in data:\n",
    "        while len(row) < max_cols:\n",
    "            row.append('')\n",
    "    df = pd.DataFrame(data[1:], columns=data[0])  # 创建DataFrame对象，第一行作为表头\n",
    "    df.replace('', pd.NA, inplace=True)  # 将空字符串替换为pd.NA\n",
    "    return df  # 返回DataFrame对象\n",
    "\n",
    "def save_to_excel(data_frames, filename):\n",
    "    \"\"\"\n",
    "    将数据帧保存到Excel文件中的不同工作表。\n",
    "\n",
    "    参数:\n",
    "    - data_frames (dict): 包含数据帧的字典，键为年份，值为数据帧。\n",
    "    - filename (str): Excel文件的路径。\n",
    "    \"\"\"\n",
    "    with pd.ExcelWriter(filename, engine='xlsxwriter') as writer:  # 创建ExcelWriter对象\n",
    "        for year, df in data_frames.items():\n",
    "            df.to_excel(writer, sheet_name=str(year), index=False)  # 将数据帧写入Excel文件的不同工作表\n",
    "with open('2309050218url.txt', 'r') as file:\n",
    "    for line in file:\n",
    "        line = line.strip()\n",
    "        url.append(line)\n",
    "print(url[0])\n",
    "print(url[1])\n",
    "print(url[2])\n",
    "def main():\n",
    "    \"\"\"\n",
    "    主函数，负责协调抓取、解析和保存过程。\n",
    "    \"\"\"\n",
    "    data_frames = {}  # 初始化数据帧字典\n",
    "    for year in range(2020, 2023):\n",
    "        url = f'https://www.kylc.com/stats/global/yearly/g_gdp/{year}.html'  # 构造URL\n",
    "        html = get_html(url)  # 获取HTML内容\n",
    "        if html:\n",
    "            df = parse_html(html)  # 解析HTML内容并生成数据帧\n",
    "            data_frames[year] = df  # 将数据帧添加到数据帧字典中\n",
    "    save_to_excel(data_frames, 'gdp_data.xlsx')  # 保存数据帧到Excel文件\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()  # 调用主函数执行程序"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16207a56",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
